package com.readfeed.app.data;

import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;
import java.util.Locale;

import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.SQLException;
import android.database.sqlite.SQLiteConstraintException;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;

import com.readfeed.app.R;
import com.readfeed.app.model.Category;
import com.readfeed.app.model.Channel;
import com.readfeed.app.model.Feed;

public class FeedsAdapter {
	public static String DATABASE_NAME = "feedsDb.db";
	private static final int DATABASE_VERSION = 1;
	
	private final Context mContext;
	private DbHelper mDbHelper;
	private SQLiteDatabase mDatabase;

	private static class DbHelper extends SQLiteOpenHelper {		
		private static DbHelper helper;
		
		public static synchronized DbHelper getInstance(Context context)
	    {
	        if(helper == null)
	        {
	            helper = new DbHelper(context);
	        }

	        return helper;
	    }
		 
		private DbHelper(Context context){
			super(context, DATABASE_NAME, null, DATABASE_VERSION);
		}
	
		@Override
		public void onCreate(SQLiteDatabase database) {
			CategoriesTable.onCreate(database);
			ChannelsTable.onCreate(database);
			FeedsTable.onCreate(database);
		}
	
		@Override
		public void onUpgrade(SQLiteDatabase database, int oldVersion, int newVersion) {
			CategoriesTable.onUpgrade(database, oldVersion, newVersion);
			ChannelsTable.onUpgrade(database, oldVersion, newVersion);
			FeedsTable.onUpgrade(database, oldVersion, newVersion);
		}					  
	}
	
	public FeedsAdapter(Context ctx){
		this.mContext = ctx;
	}
	
	public FeedsAdapter open() throws SQLException {
		this.mDbHelper = DbHelper.getInstance(mContext.getApplicationContext());
		this.mDatabase = this.mDbHelper.getWritableDatabase();
		return this;
	}
	
	public List<Category> getAllCategories(){		
		List<Category> categories = new ArrayList<Category>();
		
		Cursor cursor = mDatabase.query(CategoriesTable.TABLE_CATEGORIES_NAME, new String[]{CategoriesTable.COLUMN_ID, CategoriesTable.COLUMN_TITLE},
				null, null, null, null, CategoriesTable.COLUMN_TITLE);

		cursor.moveToFirst();
		while (!cursor.isAfterLast()) {
			Category category = cursorToCategory(cursor);
			List<Channel> channels = getChannelsByCategory(category.getId());
			category.setChannels(channels);
			categories.add(category);	
			
			cursor.moveToNext();
		}					
		
		cursor.close();
		return categories;
	}
	
	public Category createCategory(Category category){
		ContentValues values = new ContentValues();
		String categoryTitle = category.getTitle();
		values.put(CategoriesTable.COLUMN_TITLE, categoryTitle);
		
		long categoryId = mDatabase.insert(CategoriesTable.TABLE_CATEGORIES_NAME, null, values);		
		Category resultCategory = this.findCategoryById(categoryId);
		
		return resultCategory;
	}
	
	public void deleteCategories(List<Category> categories) throws ParseException{
		for (int i = 0; i < categories.size(); i++) {
			Category cat = categories.get(i);
			long id = cat.getId();
			List<Channel> catChannels = this.getFullChannelsByCategory(id);			
			this.deleteChannels(catChannels);
			mDatabase.delete(CategoriesTable.TABLE_CATEGORIES_NAME, CategoriesTable.COLUMN_ID + "=?", 
					new String[]{Long.toString(id)});
		}
	}
	
	public Channel createChannel(Channel channel, long categoryId){
		String channelTitle = channel.getTitle();
		String channelUri = channel.getUri();
		String channelDescription = channel.getDescription();
		long channelUpdateTime = channel.getLastUpdate();
		List<Feed> feeds = channel.getItems();
		
		ContentValues values = new ContentValues();
		values.put(ChannelsTable.COLUMN_TITLE, channelTitle);
		values.put(ChannelsTable.COLUMN_URI, channelUri);
		values.put(ChannelsTable.COLUMN_DESCRIPTION, channelDescription);
		values.put(ChannelsTable.COLUMN_CATEGORY_ID, categoryId);
		values.put(ChannelsTable.COLUMN_UPDATE, channelUpdateTime);
		
		mDatabase.beginTransaction();
		long channelId = -1;
		try{
			channelId = mDatabase.insert(ChannelsTable.TABLE_CHANNELS_NAME, null, values);
			this.addFeedsByChannelId(channelId, feeds);
			mDatabase.setTransactionSuccessful();
		} finally {
			mDatabase.endTransaction();
		}
		
		channel.setId(channelId);
		return channel;
	}
	
	public void addFeedsByChannelId(long channelId, List<Feed> feeds){
		for (int i = 0; i < feeds.size(); i++) {
			Feed feed = feeds.get(i);
			String feedTitle = feed.getTitle();
			String feedUrl = feed.getUrl();
			String feedSummary = feed.getSummary();
			String feedGuid = feed.getGuid();
			long feedPubDate = feed.getPubDate().getTime();
			
			ContentValues values = new ContentValues();
			values.put(FeedsTable.COLUMN_TITLE, feedTitle);
			values.put(FeedsTable.COLUMN_URI, feedUrl);
			values.put(FeedsTable.COLUMN_GUID, feedGuid);
			values.put(FeedsTable.COLUMN_PUBDATE, feedPubDate);
			values.put(FeedsTable.COLUMN_SUMMARY, feedSummary);
			values.put(FeedsTable.COLUMN_CHANNEL_ID, channelId);
			
			long insertedId = mDatabase.insert(FeedsTable.TABLE_ITEMS_NAME, null, values);
			if (insertedId == -1) {
				throw new SQLiteConstraintException(null);
			}
		}
	}
	
	public List<Channel> getFullChannelsByCategory(long categoryId) throws ParseException{
		List<Channel> channels = this.getChannelsByCategory(categoryId);
		for (int i = 0; i < channels.size(); i++) {
			Channel channel = channels.get(i);
			long channelId = channel.getId();
			List<Feed> feeds = this.getFeedsByChannelId(channelId);
			channel.setItems(feeds);
		}
		
		return channels;
	}
	
	public List<Feed> getFeedsByChannelId(long channelId) throws ParseException{
		String selectionArgs = Long.toString(channelId);
		Cursor cursor = mDatabase.query(FeedsTable.TABLE_ITEMS_NAME, 
				new String[]{FeedsTable.COLUMN_ID, 
				FeedsTable.COLUMN_TITLE,
				FeedsTable.COLUMN_URI,
				FeedsTable.COLUMN_GUID,
				FeedsTable.COLUMN_PUBDATE,
				FeedsTable.COLUMN_SUMMARY,
				FeedsTable.COLUMN_CHANNEL_ID}, 
				FeedsTable.COLUMN_CHANNEL_ID + " =?", new String[] {selectionArgs}, null, null, FeedsTable.COLUMN_PUBDATE + " DESC");
		
		cursor.moveToFirst();
		List<Feed> feedsList = new ArrayList<Feed>();
		while (!cursor.isAfterLast()) {
			Feed feed = this.cursorToFeed(cursor);
			feedsList.add(feed);
			cursor.moveToNext();
		}
		
		return feedsList;
	}
	
	public List<Feed> getFeedsToDiscard(long discardPeriod, long channelId) throws ParseException{
		long now = new Date().getTime();
		long limit = now - discardPeriod;
		Cursor cursor = mDatabase.query(FeedsTable.TABLE_ITEMS_NAME, 
				new String[]{FeedsTable.COLUMN_ID, 
				FeedsTable.COLUMN_TITLE,
				FeedsTable.COLUMN_URI,
				FeedsTable.COLUMN_GUID,
				FeedsTable.COLUMN_PUBDATE,
				FeedsTable.COLUMN_SUMMARY,
				FeedsTable.COLUMN_CHANNEL_ID}, 
				FeedsTable.COLUMN_PUBDATE + " <= ? and " + FeedsTable.COLUMN_CHANNEL_ID + " = ? ", 
				new String[] {Long.toString(limit), Long.toString(channelId)}, 
				null, null, null);
		
		cursor.moveToFirst();
		List<Feed> discardList = new ArrayList<Feed>(); 
		while (!cursor.isAfterLast()) {
			Feed feed = this.cursorToFeed(cursor);
			discardList.add(feed);
			cursor.moveToNext();
		}
		
		return discardList;
	}
	
	public Feed getFeedById(long feedId) throws ParseException{
		Cursor cursor = mDatabase.query(FeedsTable.TABLE_ITEMS_NAME, 
				new String[]{FeedsTable.COLUMN_ID, 
				FeedsTable.COLUMN_TITLE,
				FeedsTable.COLUMN_URI,
				FeedsTable.COLUMN_GUID,
				FeedsTable.COLUMN_PUBDATE,
				FeedsTable.COLUMN_SUMMARY,
				FeedsTable.COLUMN_CHANNEL_ID}, 
				FeedsTable.COLUMN_ID + " = ?" , 
				new String[] {Long.toString(feedId)}, 
				null, null, null); 
		cursor.moveToFirst();
		Feed feed = this.cursorToFeed(cursor);
		
		return feed;
	}
	
	public void deleteChannels(List<Channel> channels){	
		for (int i = 0; i < channels.size(); i++) {
			Channel channel = channels.get(i);
			List<Feed> feeds = channel.getItems();
			this.deleteFeeds(feeds);
			long id = channel.getId();
			mDatabase.delete(ChannelsTable.TABLE_CHANNELS_NAME,
					ChannelsTable.COLUMN_ID + "=?", new String[]{Long.toString(id)});
		}
	}
	
	public void deleteFeeds(List<Feed> feeds){
		for (int i = 0; i < feeds.size(); i++) {
			Feed feed = feeds.get(i);
			long id = feed.getId();
			mDatabase.delete(FeedsTable.TABLE_ITEMS_NAME,
					FeedsTable.COLUMN_ID + "=?", new String[]{Long.toString(id)});
		}
	}
	
	public void refreshChannelUpdateTime(long channelId){
		ContentValues values = new ContentValues();
		long date = (new Date()).getTime();
		values.put(ChannelsTable.COLUMN_UPDATE, date);		
		mDatabase.update(ChannelsTable.TABLE_CHANNELS_NAME, values, ChannelsTable.COLUMN_ID + "=?", 
				new String[] {Long.toString(channelId)});
	}
	
	private Feed cursorToFeed(Cursor cursor) throws ParseException{
		Feed feed = new Feed();
		feed.setId(cursor.getLong(0));
		feed.setTitle(cursor.getString(1));
		feed.setUrl(cursor.getString(2));
		feed.setGuid(cursor.getString(3));
		
		long pubDate = cursor.getLong(4);
		String pattern = mContext.getResources().getString(R.string.pubdate_format_pattern);
		SimpleDateFormat df = new SimpleDateFormat(pattern, Locale.US);
		String dateToString = df.format(pubDate);
		Date date = df.parse(dateToString);
		
		feed.setPubDate(date);
		feed.setSummary(cursor.getString(5));
		feed.setChannelId(cursor.getLong(6));
		
		return feed;
	}
	
	private List<Channel> getChannelsByCategory(long categoryId){
		String selectionArg = Long.toString(categoryId); 
		Cursor cursor = mDatabase.query(ChannelsTable.TABLE_CHANNELS_NAME, 
				new String[]{ChannelsTable.COLUMN_ID,
				ChannelsTable.COLUMN_TITLE, 
				ChannelsTable.COLUMN_URI, 
				ChannelsTable.COLUMN_DESCRIPTION, 
				ChannelsTable.COLUMN_CATEGORY_ID,
				ChannelsTable.COLUMN_UPDATE},
				ChannelsTable.COLUMN_CATEGORY_ID + " =?", new String[]{selectionArg}, null, null, null);
		
		cursor.moveToFirst();		
		List<Channel> channelsList = new ArrayList<Channel>();		
		while (!cursor.isAfterLast()) {
			Channel channel = cursorToChannel(cursor);
			channelsList.add(channel);	
			cursor.moveToNext();
		}
		
		cursor.close();
		return channelsList;
	}	
	
	private Channel cursorToChannel(Cursor cursor){
		Channel channel = new Channel();
		channel.setId(cursor.getLong(0)); 
		channel.setTitle(cursor.getString(1));		
		channel.setUri(cursor.getString(2));
		channel.setDescription(cursor.getString(3));
		channel.setCategoryId(cursor.getLong(4));
		channel.setUpdateTime(cursor.getLong(5));
		
		return channel;
	}
	
	private Category cursorToCategory(Cursor cursor){
		Category category = new Category();
		category.setTitle(cursor.getString(1));
		category.setId(cursor.getLong(0));
		
		return category;
	}
	
	private Category findCategoryById(long categoryId){
		Cursor cursor = mDatabase.query(CategoriesTable.TABLE_CATEGORIES_NAME, new String[]{CategoriesTable.COLUMN_ID, CategoriesTable.COLUMN_TITLE}, 
				CategoriesTable.COLUMN_ID + " = ?", new String[] {Long.toString(categoryId)}, null, null, null);
		cursor.moveToFirst();
		
		Category resultCategory = this.cursorToCategory(cursor);
		cursor.close();
		return resultCategory;
	}
}